package entity;

import javax.ejb.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import transfer.Group;
import transfer.User;

/**
 *
 * @author Sergey
 */
public class Groups implements EntityBean {

    private DataSource dataSource;
    private EntityContext entityContext;
    private Group group;

    public Group getGroup() {
        return group;
    }

    public void setGroup(Group agroup) {
        this.group = agroup;
    }

    public void setEntityContext(EntityContext entityContext) throws EJBException {
        try {
            this.entityContext = entityContext;
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/ossystem");
        } catch (NamingException ex) {
            throw new EJBException("Does not find jdbc/ossystem");
        }
    }

    public void unsetEntityContext() {
        entityContext = null;
        dataSource = null;
    }

    public void ejbRemove() throws RemoveException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "DELETE FROM USER_LINK_GROUP WHERE id_group = ?");
            statement.setInt(1, group.getIdGroup().intValue());
            statement.executeUpdate();
            statement.close();

            statement = connection.prepareStatement(
                    "DELETE FROM Groups WHERE id_group = ?");
            statement.setInt(1, group.getIdGroup().intValue());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbActivate() {
    }

    public void ejbPassivate() {
    }

    public void ejbLoad() throws EJBException {
        Integer primaryKey = (Integer) entityContext.getPrimaryKey();
        Connection connection = null;
        PreparedStatement statement = null;
        group = new Group();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_user FROM USER_LINK_GROUP WHERE id_group = ?");
            statement.setInt(1, primaryKey.intValue());
            ResultSet resultSet = statement.executeQuery();
            Context c = new InitialContext();
            UsersLocalHome userHome = (UsersLocalHome) c.lookup("java:comp/env/Users");
            ArrayList users = new ArrayList();
            while (resultSet.next()) {
                UsersLocal userLocal = userHome.findByPrimaryKey(new Integer(resultSet.getInt(1)));
                users.add(userLocal.getUser());
            }
            statement.close();
            group.setUsers(users);
            statement = connection.prepareStatement(
                    "SELECT id_group, name_group FROM GROUPS WHERE id_group = ?");
            statement.setInt(1, primaryKey.intValue());
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                SQLException e = new SQLException("Group with id = " + primaryKey + " not exists");
                e.printStackTrace();
            }
            group.setIdGroup(new Integer(resultSet.getInt(1)));
            group.setNameGroup(resultSet.getString(2));
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbStore() throws EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            Integer idGroup = (Integer) entityContext.getPrimaryKey();
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "UPDATE Groups SET name_group = ? "
                    + "WHERE id_group = ?");
            statement.setString(1, group.getNameGroup());
            statement.setInt(2, idGroup.intValue());
            statement.executeUpdate();
            statement.close();
            statement = connection.prepareStatement("DELETE FROM User_link_Group WHERE id_group = ?");
            statement.setInt(1, idGroup.intValue());
            statement.executeUpdate();
            statement.close();
            ArrayList users = group.getUsers();
            InitialContext context = new InitialContext();
            UsersLocalHome userHome = (UsersLocalHome) context.lookup("java:comp/env/Users");
            for (Iterator iterator = users.iterator(); iterator.hasNext();) {
                User user = (User) iterator.next();
                UsersLocal userLocal = userHome.findByPrimaryKey(user.getIdUser());
                userLocal.setUser(user);
                statement = connection.prepareStatement("INSERT INTO User_link_Group(id_user, id_group) VALUES (?, ?)");
                statement.setInt(1, user.getIdUser().intValue());
                statement.setInt(2, idGroup.intValue());
                statement.executeUpdate();
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NamingException ex) {
            ex.printStackTrace();
        } catch (FinderException ex) {
            ex.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindAllGroups() throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_group FROM Groups");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByPrimaryKey(Integer idGroup) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_group "
                    + "FROM Groups WHERE id_group = ?");
            statement.setInt(1, idGroup.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Group with primary key " + idGroup + " is not exists");
            }
            return new Integer(resultSet.getInt(1));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindByUser(User user) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_group FROM USER_LINK_GROUP WHERE id_user = ?");
            statement.setInt(1, user.getIdUser().intValue());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbPostCreate(Group agroup) throws CreateException {
    }

    public Integer ejbCreate(Group agroup) throws CreateException {
        Connection connection = null;
        PreparedStatement statement = null;
        group = new Group();
        try {
            connection = dataSource.getConnection();
            Statement stat = connection.createStatement();
            Integer idGroup = null;
            ResultSet resultSet = stat.executeQuery("select seq.nextval from dual");
            if (resultSet.next()) {
                idGroup = new Integer(resultSet.getInt(1));
            }
            stat.close();
            group.setIdGroup(idGroup);
            group.setNameGroup(agroup.getNameGroup());
            group.setUsers(agroup.getUsers());
            statement = connection.prepareStatement("INSERT INTO Groups"
                    + "(id_group, name_group) VALUES(?, ?)");
            statement.setInt(1, idGroup.intValue());
            statement.setString(2, agroup.getNameGroup());
            if (statement.executeUpdate() != 1) {
                throw new CreateException("Inserted error");
            }
            statement.close();
            ArrayList users = agroup.getUsers();
            for (int i = 0; i < users.size(); i++) {
                User user = ((User) (users.get(i)));
                statement = connection.prepareStatement(
                        "INSERT INTO USER_LINK_GROUP(id_user,id_group) VALUES(?, ?)");
                statement.setInt(1, user.getIdUser().intValue());
                statement.setInt(2, idGroup.intValue());
                if (statement.executeUpdate() != 1) {
                    throw new CreateException("Inserted error");
                }
                statement.close();
            }
            return idGroup;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

//    private void addUser(User user) throws CreateException {
//        Connection connection = null;
//        PreparedStatement statement = null;
//        try {
//            connection = dataSource.getConnection();
//            Statement stat = connection.createStatement();
//            statement = connection.prepareStatement("INSERT INTO USER_LINK_GROUP"
//                    + "(id_user, id_group) VALUES(?, ?)");
//            statement.setInt(1, user.getIdUser().intValue());
//            statement.setInt(2, ((Integer) entityContext.getPrimaryKey()).intValue());
//            if (statement.executeUpdate() != 1) {
//                throw new CreateException("Inserted error");
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            closeConnection(connection, statement);
//        }
//    }
//
//    private void deleteUser(User user) throws RemoveException {
//        Connection connection = null;
//        PreparedStatement statement = null;
//        try {
//            connection = dataSource.getConnection();
//            statement = connection.prepareStatement(
//                    "DELETE FROM USER_LINK_GROUP WHERE id_user = ?");
//            statement.setInt(1, user.getIdUser().intValue());
//            if (statement.executeUpdate() < 1) {
//                throw new RemoveException("deleting error");
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            closeConnection(connection, statement);
//        }
//    }
    private void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
